package cn.gson.crm.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import com.mysql.jdbc.StringUtils;

import cn.gson.crm.model.entity.Employee;
import cn.gson.crm.model.po.FollowStudentPo;
import cn.gson.crm.model.po.StudentPayPo;

public class StudentPayDao {
	private int pageSize = 3;

	/**
	 * 学生缴费
	 * 
	 * @param po
	 * @return
	 * @throws SQLException
	 */
	public Boolean createPayRecord(StudentPayPo po) throws SQLException {
		String sql = "insert into pay(stu_id,emp_id,pay_id,due_pay,this_pay,pay_way) values(?,?,CONCAT('trkjStuPay',DATE_FORMAT(now(),'%Y%m%d'),LPAD(LAST_INSERT_ID()+1,20,'0')),?,?,?)";
		return Jdbc.getInstace().executeUpdate(sql, po.getId(), po.getEmpId(), po.getDuePay(), po.getThisPay(),
				po.getPayWay());
	}

	/**
	 * 通过学生id查询到收款记录
	 * 
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public StudentPayPo getPayRecord(String id) throws SQLException {
		String sql = "select *,SUM(this_pay)have_pay,(due_pay-SUM(this_pay))as no_pay, count(*)total from pay where stu_id=? ";
		Map<String, Object> row = Jdbc.getInstace().executeQuery(sql, id);
		return buildStudentDetail(row);
	}

	/**
	 * 获取学生交款记录和根据日期查询收款记录 员工名字精确查询
	 * 
	 * @param page
	 * @param stuId
	 * @param payDate
	 * @return
	 * @throws SQLException
	 */
	public List<StudentPayPo> getPayList(int page, Integer stuId, Integer empId) throws SQLException {
		int start = (page - 1) * pageSize;
		String sql = null;
		List<Object> params = new ArrayList<>();
		sql = "select * from pay ";
		if (stuId != null) {
			params.add(stuId);
			sql += " where stu_id=?";
			if (empId != null) {
				sql += " and emp_id= ? ";
				params.add(empId);
			}else{
				sql += " and emp_id= 0 ";
			}
		} else {
			if (empId != null) {
				sql += " where emp_id= ? ";
				params.add(empId);
			} else {
				sql = " where stu_id=0 ";
			}
		}
		sql += " order by pay_date desc limit ?,? ";
		params.add(start);
		params.add(pageSize);
		List<Map<String, Object>> rows = Jdbc.getInstace().executeQueryForList(sql, params.toArray());
		List<StudentPayPo> poList = new ArrayList<>();
		return buildStudentList(rows, poList);
	}

	/**
	 * 获取学生交款记录和根据日期查询收款记录 员工名字精确查询和时间查询
	 * 
	 * @param page
	 * @param stuId
	 * @param empId
	 * @param startTime
	 * @param endTime
	 * @return
	 * @throws SQLException
	 */
	public List<StudentPayPo> getPayList(int page, Integer stuId, Integer empId, Date startTime, Date endTime)
			throws SQLException {
		int start = (page - 1) * pageSize;
		String sql = null;
		List<Object> params = new ArrayList<>();
		if (startTime == endTime) {
			sql = "select * from pay where  pay_date =?";
			if (stuId != null) {
				params.add(stuId);
				sql += " and stu_id=? ";
			}
			if (empId != null) {
				sql += " and emp_id= ? ";
				params.add(empId);
				params.add(startTime);
			}
		} else {
			params.add(startTime);
			params.add(endTime);
			sql = "select * from pay where  pay_date between ? and ? ";
			if (stuId != null) {
				params.add(stuId);
				sql += " and stu_id=? ";
			}
			if (empId != null) {
				sql += " and emp_id= ? ";
				params.add(empId);
			}

		}
		sql += " order by pay_date desc limit ?,? ";
		params.add(start);
		params.add(pageSize);
		List<Map<String, Object>> rows = Jdbc.getInstace().executeQueryForList(sql, params.toArray());
		List<StudentPayPo> poList = new ArrayList<>();
		return buildStudentList(rows, poList);

	}

	/**
	 * 获取学生交款记录和根据日期查询收款记录总页数 员工名字精确查询
	 * 
	 * @param stuId
	 * @param payDate
	 * @return
	 * @throws SQLException
	 */
	public Integer getPayTotalPage(Integer stuId, Integer empId) throws SQLException {
		String sql = null;
		List<Object> params = new ArrayList<>();
		sql = "select count(*)total from pay ";
		if (stuId != null) {
			params.add(stuId);
			sql += " where stu_id=?";
			if (empId != null) {
				sql += " and emp_id= ? ";
				params.add(empId);
			}else{
				sql += " and emp_id= 0 ";
			}
		} else {
			if (empId != null) {
				sql += " where emp_id= ? ";
				params.add(empId);
			} else {
				sql = " where stu_id=0 ";
			}
		}
		Map<String, Object> row = Jdbc.getInstace().executeQuery(sql, params.toArray());
		Integer count = 0;
		if (row != null) {
			count = Integer.parseInt(row.get("total").toString());
		}
		return (int) Math.ceil(count.intValue() / pageSize);
	}

	/**
	 * 获取学生交款记录和根据日期查询收款记录总页数 员工名字精确查询和时间查询
	 * 
	 * @param stuId
	 * @param empId
	 * @param startTime
	 * @param endTime
	 * @return
	 * @throws SQLException
	 */
	public Integer getPayTotalPage(Integer stuId, Integer empId, Date startTime, Date endTime) throws SQLException {
		String sql = null;
		List<Object> params = new ArrayList<>();
		if (startTime == endTime) {
			sql = "select count(*) from pay where  pay_date =?";
			if (stuId != null) {
				params.add(stuId);
				sql += " and stu_id=? ";
			}
			if (empId != null) {
				sql += " and emp_id= ? ";
				params.add(empId);
				params.add(startTime);
			}
		} else {
			params.add(startTime);
			params.add(endTime);
			sql = "select count(*)total from pay where  pay_date between ? and ? ";
			if (stuId != null) {
				params.add(stuId);
				sql += " and stu_id=? ";
			}
			if (empId != null) {
				sql += " and emp_id= ? ";
				params.add(empId);
			}
		}

		Map<String, Object> row = Jdbc.getInstace().executeQuery(sql, params.toArray());
		Integer count = 0;
		if (row != null) {
			count = Integer.parseInt(row.get("total").toString());
		}
		return (int) Math.ceil(count.intValue() / pageSize);
	}

	/**
	 * 获取状态为status,缴费情况为pay
	 * 
	 * @param status
	 * @param stuName
	 * @return
	 * @throws SQLException
	 */
	public List<StudentPayPo> getStudentList(int page, String status, String isAllocation, String stuName, int isPay)
			throws SQLException {
		int start = (page - 1) * pageSize;
		String sql = "select * from(select * from (select * from student where stu_status = ? and is_allocation =? and grade_id>4 ";
		List<Object> params = new ArrayList<>();
		params.add(status);
		params.add(isAllocation);
		if (!StringUtils.isNullOrEmpty(stuName)) {
			sql += " and stu_name like ?";
			params.add("%" + stuName.trim() + "%");
		}

		sql += ") as form  where form.is_allpay=?) as totalform join contract on contract.stu_id= totalform.id order by contract.contract_createtime desc limit ?,?";
		params.add(isPay);
		params.add(start);
		params.add(pageSize);
		List<Map<String, Object>> rows = Jdbc.getInstace().executeQueryForList(sql, params.toArray());
		List<StudentPayPo> studentList = null;
		return buildStudentList(rows, studentList);
	}

	/**
	 * 获取状态为status,交款情况为isAllPay，等级gradeId>4的学生总页数
	 * 
	 * @param status
	 * @param isAllocation
	 * @param stuName
	 * @param gradeId
	 * @return
	 * @throws SQLException
	 */
	public int getTotalPage(String status, String isAllocation, String stuName, int isAllPay) throws SQLException {
		String sql = "select count(*)total from(select * from (select * from student where stu_status = ? and is_allocation =? and grade_id>4 ";
		List<Object> params = new ArrayList<>();
		params.add(status);
		params.add(isAllocation);
		if (!StringUtils.isNullOrEmpty(stuName)) {
			sql += " and stu_name like ?";
			params.add("%" + stuName.trim() + "%");
		}
		sql += ") as form  where form.is_allpay=?) as totalform join contract on contract.stu_id= totalform.id ";
		params.add(isAllPay);
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, params.toArray());
		Integer count = 0;
		if (data != null) {
			count = Integer.parseInt(data.get("total").toString());
		}
		return (int) Math.ceil(count.doubleValue() / pageSize);
	}

	/**
	 * 学生签约
	 * 
	 * @param stuId
	 * @param empId
	 * @return
	 * @throws SQLException
	 */
	public Boolean getContract(StudentPayPo po) throws SQLException {
		String sql = "insert into contract(contract_id,stu_id,emp_id) values(CONCAT('trkjstucon',DATE_FORMAT(now(),'%Y%m%d'),LPAD(LAST_INSERT_ID()+1,20,'0')),?,?)";
		return Jdbc.getInstace().executeUpdate(sql, po.getId(), po.getEmpId());
	}

	/**
	 * 组装学员信息列表
	 * 
	 * @param dataList
	 * @return
	 * @throws SQLException
	 */
	private List<StudentPayPo> buildStudentList(List<Map<String, Object>> rows, List<StudentPayPo> studentdetailList)
			throws NumberFormatException, SQLException {
		studentdetailList = new ArrayList<>();
		if (rows != null) {
			for (Map<String, Object> row : rows) {
				studentdetailList.add(buildStudentDetail(row));
			}
		}
		return studentdetailList;
	}

	/**
	 * 组装学生信息
	 * 
	 * @param data
	 * @return
	 */
	private StudentPayPo buildStudentDetail(Map<String, Object> data) throws SQLException {
		StudentPayPo po = null;
		if (data != null) {
			po = new StudentPayPo();
			if (data.get("id") != null) {
				po.setId(Integer.parseInt(data.get("id").toString()));
			}
			po.setIsAllocation((String) data.get("is_allocation"));
			po.setName((String) data.get("stu_name"));
			po.setGender((String) data.get("stu_gender"));
			po.setSource((String) data.get("stu_source"));
			po.setEducation((String) data.get("stu_education"));
			po.setPhone((String) data.get("stu_phone"));
			po.setAddress((String) data.get("stu_address"));
			po.setIdCard((String) data.get("stu_idcard"));
			po.setQq((String) data.get("stu_qq"));
			po.setFatherName((String) data.get("stu_fathername"));
			po.setFatherTel((String) data.get("stu_fathertel"));
			po.setMotherName((String) data.get("stu_mothername"));
			po.setMotherTel((String) data.get("stu_mothertel"));
			po.setCreator((String) data.get("stu_creator"));

			po.setCreateTime((Date) data.get("stu_createtime"));
			if (data.get("grade_id") != null) {
				po.setGradeId(Integer.parseInt(data.get("grade_id").toString()));
			}
			if (data.get("counselor_id") != null) {
				po.setCounselorId(Integer.parseInt(data.get("counselor_id").toString()));
				// 获取咨询师姓名
				Employee emp = new EmploDao().emploGetById(Integer.parseInt(data.get("counselor_id").toString()));
				if (emp != null) {
					po.setCounselorName(data.get("counselor_id").toString() + emp.getUserName());
				}
			}
			po.setStatus((String) data.get("stu_status"));

		}

		if (data.get("emp_id") != null) {
			po.setEmpId(Integer.parseInt(data.get("emp_id").toString()));
			Employee emp = new EmploDao().emploGetById(Integer.parseInt(data.get("emp_id").toString()));
			if (emp != null) {
				po.setEmpName(data.get("emp_id").toString() + emp.getUserName());
			}
		}
		if (data.get("p_id") != null) {
			po.setpId(Integer.parseInt(data.get("p_id").toString()));
		}
		po.setPayOrderId((String) data.get("pay_id"));
		if (data.get("emp_id") != null) {
			po.setEmpId(Integer.parseInt(data.get("emp_id").toString()));
		}
		if (data.get("due_pay") != null) {
			po.setDuePay(Integer.parseInt(data.get("due_pay").toString()));
		}

		if (data.get("this_pay") != null) {
			po.setThisPay(Integer.parseInt(data.get("this_pay").toString()));
		}
		po.setPayDate((Date) data.get("pay_date"));
		if (data.get("no_pay") != null) {
			po.setNoPay(Integer.parseInt(data.get("no_pay").toString()));
		}
		if (data.get("have_pay") != null) {
			po.setHavePay(Integer.parseInt(data.get("have_pay").toString()));
		}
		if (data.get("cid") != null) {
			po.setcId(Integer.parseInt(data.get("c_id").toString()));

		}
		po.setContactOrderId((String) data.get("contract_id"));
		po.setContractDate((Date) (data.get("contract_createtime")));
		if (data.get("pay_way") != null) {
			po.setPayWay(data.get("pay_way").toString());
			po.setPayWayText(data.get("pay_way").toString());
		}

		if (data.get("class_type") != null) {
			po.setClassType(Integer.parseInt(data.get("class_type").toString()));
		}
		if (data.get("total") != null) {
			po.setPayCount(Integer.parseInt(data.get("total").toString()));
		}
		return po;
	}

	/**
	 * 取到集合之后再进行分页
	 * 
	 * @param request
	 * @param list
	 * @param pageSize
	 * @param page
	 * @return
	 */
	public List<StudentPayPo> getListThenDividPage(HttpServletRequest request, List<StudentPayPo> list, int page) {
		List<StudentPayPo> subList = null;
		Integer totalCount = list.size();
		/* 计算出总共能分成多少页 */
		int pageCount = totalCount % pageSize == 0 ? totalCount / pageSize : totalCount / pageSize + 1;
		if (totalCount > 0) {
			if (page <= pageCount) {
				// 当前页数为第一页
				if (page == 1) {
					// 数据总数小于每页显示的数据条数
					if (totalCount <= pageSize) {
						// 截止到总的数据条数(当前数据不足一页，按一页显示)，这样才不会出现数组越界异常
						subList = list.subList(0, totalCount);
					} else {
						subList = list.subList(0, pageSize);
					}
				} else {
					// 截取起始下标
					int fromIndex = (page - 1) * pageSize;
					// 截取截止下标
					int toIndex = page * pageSize;
					/* 计算截取截止下标 */
					if ((totalCount - toIndex) % pageSize >= 0) {
						toIndex = page * pageSize;
					} else {
						toIndex = (page - 1) * pageSize + (totalCount % pageSize);
					}
					if (totalCount >= toIndex) {
						subList = list.subList(fromIndex, toIndex);
					}
				}
			} else {
				subList = null;
			}
		}
		request.setAttribute("totalPage", pageCount);
		return subList;

	}

}
